Tidy Data

Tidy data…

• is rectangular.
• has observations as rows and variables as columns.
• has different formats for different tasks.

R4DS

Consequences of Messy Data

Allison Horst

• Tidy: use the same tools in similar ways for different datasets.
• Messy: create unique tools that are difficult to generalize.

Creating Tidy Data

We may need to transform our data to turn it into the version of tidy that is best for a task at hand.

Allison Horst

Creating Tidy Data

Let’s say we want to look at mean cereal nutrients based on shelf.

library(liver)
data(cereal)
head(cereal)

Creating Tidy Data

cereal_wide <- cereal |> 
  group_by(shelf) |> 
  summarise(across(calories:vitamins, mean))

my_colors <- c("calories_col" = "steelblue", "sugars_col" = "orange3")

cereal_wide |> 
  ggplot() +
  geom_point(aes(x = shelf, y = calories, color = "calories_col")) +
  geom_line(aes(x = shelf, y = calories, color = "calories_col")) + 
  geom_point(aes(x = shelf, y = sugars, color = "sugars_col")) +
  geom_line(aes(x = shelf, y = sugars, color = "sugars_col")) +
  scale_color_manual(values = my_colors, labels = names(my_colors)) +
  labs(x = "Shelf", y = "", subtitle = "Mean Amount", color = "Nutrient")

cereal_long<- cereal |> 
  pivot_longer(cols = calories:vitamins,
               names_to = "Nutrient",
               values_to = "Amount") |> 
  group_by(shelf, Nutrient) |> 
  summarise(mean_amount = mean(Amount))

cereal_long |> 
  ggplot(aes(x = shelf, 
             y = mean_amount, 
             color = Nutrient)) +
  geom_point() +
  geom_line() +
  labs(x = "Shelf", y = "", subtitle = "Mean Amount")

Manual Method

Consider daily rainfall observed in SLO in January 2023.

• The data is in a human-friendly form (like a calendar).
• Each week has a row, and each day has a column.

Data source

How would you manually convert this to long format?

Manual Method: Steps

Manual Method: Steps

0. Keep the column Week.
1. Create a new column Day_of_Week.
2. Create a new column Rainfall (hold daily rainfall values).
3. Now we have three columns – move Sunday values over.
4. Duplicate Week 1-5 and copy Monday values over.

Manual Method: Steps

0. Keep the column Week.
1. Create a new column Day_of_Week.
2. Create a new column Rainfall (hold daily rainfall values).
3. Now we have three columns – move Sunday values over.
4. Duplicate Week 1-5 and copy Monday values over.
5. Duplicate Week 1-5 and copy Tuesday values over.

Manual Method: Steps

0. Keep the column Week.
1. Create a new column Day_of_Week.
2. Create a new column Rainfall (hold daily rainfall values).
3. Now we have three columns – move Sunday values over.
4. Duplicate Week 1-5 and copy Monday values over.
5. Duplicate Week 1-5 and copy Tuesday values over.
6. Continue for the rest of the days of the week.

Manual Method: Steps

0. Keep the column Week.
1. Create a new column Day_of_Week.
2. Create a new column Rainfall (hold daily rainfall values).
3. Now we have three columns – move Sunday values over.
4. Duplicate Week 1-5 and copy Monday values over.
5. Duplicate Week 1-5 and copy Tuesday values over.
6. Continue for the rest of the days of the week.
7. You may want to arrange() by Week to get the rainfall values chronological order.

Computational Approach

We can use pivot_longer() to turn a wide dataset into a long(er) dataset.

pivot_longer()

Take a wide dataset and turn it into a long daaset.

• cols – specify the columns that should be pivoted.
  • Do not include the names of ID columns (columns to not be pivoted).
• names_to – the name of the new column containing the old column names.
• values_to – the name of the new column containing the old column values.

pivot_longer()

slo_rainfall |> 
  pivot_longer(cols      = Sunday:Saturday,
               names_to  = "Day_of_Week",
               values_to = "Daily_Rainfall")

pivot_wider()

Take a long dataset and turn it into a wide daaset.

• id_cols – specify the column(s) that contain the ID for unique rows in the wide dataset.
• names_from – the name of the column containing the new column names.
• values_from – the name of the column containing the new column values.

pivot_wider()

Let’s say we calculate the mean amount of protein for cereals on each shelf and for each manuf.

mean_protein <- cereal |> 
  group_by(manuf, shelf) |> 
  summarize(mean_protein = mean(protein))

pivot_wider()

We can make this dataset more easily readable…

mean_protein |> 
  arrange(shelf) |> 
  pivot_wider(id_cols = manuf,
              names_from = shelf,
              values_from = mean_protein)

Better names in pivot_wider()

mean_protein |> 
  arrange(shelf) |> 
  pivot_wider(id_cols = manuf,
              names_from = shelf,
              values_from = mean_protein,
              names_prefix = "Shelf_")

Relational Data

Multiple, interconnected tables of data are called relational.

• It is the relation between datasets, not just the individual datasets themselves, that are important.

IMDb movie relational data

Data Joins

We can combine (join) data tables based on their relations.

Keys

A key uniquely identifies an observation in a data set.

• To combine (join) two datasets, the key needs to be present in both.

inner_join()

Keeps obsertvations when their keys are present in both datasets.

inner_join(): IMDb Example

directors_genres

movies_directors

inner_join(directors_genres, movies_directors)

inner_join(): IMDb Example

What if our key does not have the same name?

directors_genres

directors

inner_join(directors_genres, 
           directors, 
           join_by(director_id == id))

Piping Joins

Remember: the dataset you pipe in becomes the first argument of the function you are piping into!

• So if you are using a pipe, you will only be specifying the right dataset inside the join function.

inner_join(directors_genres, movies_directors)

directors_genres |> 
  inner_join(movies_directors)

More Mutating Joins

• left_join() – keep only (and all) observations present in the left data set

• right_join() – keep only (and all) observations present in the right data set

• full_join() – keep only (and all) observations present in both data sets

More Mutating Joins

Which directors would remain for each of the following?

directors_genres |> 
  distinct(director_id)

movies_directors |> 
  distinct(director_id)

Filtering Joins: semi_join()

Keeps observations when their keys are present in both datasets, but only keeps variables from the first dataset.

Filtering Joins: semi_join()

directors_genres |> 
  semi_join(movies_directors)

directors_genres |>
  filter(director_id %in% movies_directors$director_id)

Filtering Joins: anti_join()

Removes observations when their keys are present in both datasets, and only keeps variables from the first dataset.

Filtering Joins: anti_join()

directors_genres |> 
  anti_join(movies_directors)

directors_genres |>
  filter(!director_id %in% movies_directors$director_id)

PA 4: Military Spending

Today you will be tidying messy data to explore the relationship between countries of the world and military spending.

• Due Wednesday, 4/24 at 10:00am

To do…

• PA 4: Military Spending
  • Due Wednesday, 4/24 at 10:00am

Wednesday, April 24

Today we will…

• New Material
  • Extensions to Relational Data
  • Factors with forcats
  • Clean Variable Names
  • Lifecycle Stages
• Lab 4: Childcare Costs in California

Relational Data

When we work with multiple tables of data, we say we are working with relational data.

• It is the relations, not just the individual datasets, that are important.

IMDb Movies Data

How can we find each director’s active years?

Joining Multiple Data Sets

directors[1:4,]

# A tibble: 4 × 3
     id first_name last_name
  <dbl> <chr>      <chr>    
1   429 Andrew     Adamson  
2  2931 Darren     Aronofsky
3  9247 Zach       Braff    
4 11652 James (I)  Cameron  

movies_directors[1:4,]

# A tibble: 4 × 2
  director_id movie_id
        <dbl>    <dbl>
1         429   300229
2        2931   254943
3        9247   124110
4       11652    10920

movies[1:4,]

# A tibble: 4 × 4
     id name           year  rank
  <dbl> <chr>         <dbl> <dbl>
1 10920 Aliens         1986  8.20
2 17173 Animal House   1978  7.5 
3 18979 Apollo 13      1995  7.5 
4 30959 Batman Begins  2005 NA   

movies_directors |> 
  inner_join(directors, 
             join_by(director_id == id))

movies_directors |> 
  inner_join(directors, 
             join_by(director_id == id)) |> 
  inner_join(movies,
             join_by(movie_id == id)) |> 
  rename(movie_name = name)

Joining on Multiple Variables

Consider the rodent data from Lab 2.

• We want to add species_id to the rodent measurements.

species

measurements

species |> 
  full_join(measurements,
            join_by(species == species, 
                    genus == genus_name))

What is a factor variable?

Factors are used for

1. categorical variables with a fixed and known set of possible values.

• E.g., day_born = Sunday, Monday, Tuesday, …, Saturday

2. displaying character vectors in non-alphabetical order.

Eras Tour

Let’s consider songs that Taylor Swift played on her Eras Tour.

I have randomly selected 25 songs (and their albums) to consider.

eras_data |> 
  slice_head(n = 10)

# A tibble: 10 × 2
   Song               Album     
   <chr>              <chr>     
 1 22                 Red       
 2 ...Ready for It?   Reputation
 3 The Archer         Lover     
 4 Bejeweled          Midnights 
 5 Style              1989      
 6 You Belong With Me Fearless  
 7 Don't Blame Me     Reputation
 8 illicit affairs    Folklore  
 9 Lavender Haze      Midnights 
10 marjorie           Evermore  

Creating a Factor – Base R

A character vector:

eras_data |> 
  pull(Album)

 [1] "Red"        "Reputation" "Lover"      "Midnights"  "1989"      
 [6] "Fearless"   "Reputation" "Folklore"   "Midnights"  "Evermore"  
[11] "Evermore"   "Lover"      "Lover"      "Red"        "Reputation"
[16] "Reputation" "Speak Now"  "Red"        "Midnights"  "Fearless"  
[21] "1989"       "Midnights"  "Fearless"   "Folklore"   "Lover"     

eras_data |> 
  pull(Album) |> 
  factor()

 [1] Red        Reputation Lover      Midnights  1989       Fearless  
 [7] Reputation Folklore   Midnights  Evermore   Evermore   Lover     
[13] Lover      Red        Reputation Reputation Speak Now  Red       
[19] Midnights  Fearless   1989       Midnights  Fearless   Folklore  
[25] Lover     
9 Levels: 1989 Evermore Fearless Folklore Lover Midnights Red ... Speak Now

Creating a Factor – Base R

When you create a factor variable from a vector…

• Every unique element in the vector becomes a level.
• The levels are ordered alphabetically.
• The elements are no longer displayed in quotes.

Creating a Factor – Base R

You can specify the order of the levels with the level argument.

eras_data |> 
  pull(Album) |> 
  factor(levels = c("Fearless","Speak Now","Red","1989",
                    "Reputation","Lover","Folklore",
                    "Evermore","Midnights"))

 [1] Red        Reputation Lover      Midnights  1989       Fearless  
 [7] Reputation Folklore   Midnights  Evermore   Evermore   Lover     
[13] Lover      Red        Reputation Reputation Speak Now  Red       
[19] Midnights  Fearless   1989       Midnights  Fearless   Folklore  
[25] Lover     
9 Levels: Fearless Speak Now Red 1989 Reputation Lover Folklore ... Midnights

forcats

Creating a Factor – fct

With fct(), the levels are automatically ordered in the order of first appearance.

eras_data |> 
  pull(Album) |> 
  fct()

 [1] Red        Reputation Lover      Midnights  1989       Fearless  
 [7] Reputation Folklore   Midnights  Evermore   Evermore   Lover     
[13] Lover      Red        Reputation Reputation Speak Now  Red       
[19] Midnights  Fearless   1989       Midnights  Fearless   Folklore  
[25] Lover     
9 Levels: Red Reputation Lover Midnights 1989 Fearless Folklore ... Speak Now

Creating a Factor – NOTE

To change a column type to factor, you must wrap fct() in a mutate() call.

eras_data <- eras_data |> 
  mutate(Album = fct(Album))

eras_data |> 
  pull(Album) |> 
  fct()

 [1] Red        Reputation Lover      Midnights  1989       Fearless  
 [7] Reputation Folklore   Midnights  Evermore   Evermore   Lover     
[13] Lover      Red        Reputation Reputation Speak Now  Red       
[19] Midnights  Fearless   1989       Midnights  Fearless   Folklore  
[25] Lover     
9 Levels: Red Reputation Lover Midnights 1989 Fearless Folklore ... Speak Now

Creating a Factor – fct

You can still specify the order of the levels with level.

eras_data |> 
  pull(Album) |> 
  fct(levels = c("Fearless","Speak Now","Red","1989",
                 "Reputation","Lover","Folklore",
                 "Evermore","Midnights"))

 [1] Red        Reputation Lover      Midnights  1989       Fearless  
 [7] Reputation Folklore   Midnights  Evermore   Evermore   Lover     
[13] Lover      Red        Reputation Reputation Speak Now  Red       
[19] Midnights  Fearless   1989       Midnights  Fearless   Folklore  
[25] Lover     
9 Levels: Fearless Speak Now Red 1989 Reputation Lover Folklore ... Midnights

Creating a Factor – fct

You can also specify non-present levels.

eras_data |> 
  pull(Album) |> 
  fct(levels = c("Taylor Swift",
                 "Fearless","Speak Now","Red","1989",
                 "Reputation","Lover","Folklore",
                 "Evermore","Midnights",
                 "The Tortured Poets Department")) 

 [1] Red        Reputation Lover      Midnights  1989       Fearless  
 [7] Reputation Folklore   Midnights  Evermore   Evermore   Lover     
[13] Lover      Red        Reputation Reputation Speak Now  Red       
[19] Midnights  Fearless   1989       Midnights  Fearless   Folklore  
[25] Lover     
11 Levels: Taylor Swift Fearless Speak Now Red 1989 Reputation ... The Tortured Poets Department

Re-coding a Factor – fct_recode

Oops, we have a typo in some of our levels! We change existing levels with the syntax <new level> = <old level>.

eras_data |>
  pull(Album) |> 
  fct_recode("folklore" = "Folklore",
             "evermore" = "Evermore",
             "reputation" = "Reputation")

 [1] Red        reputation Lover      Midnights  1989       Fearless  
 [7] reputation folklore   Midnights  evermore   evermore   Lover     
[13] Lover      Red        reputation reputation Speak Now  Red       
[19] Midnights  Fearless   1989       Midnights  Fearless   folklore  
[25] Lover     
11 Levels: Taylor Swift Fearless Speak Now Red 1989 reputation ... The Tortured Poets Department

Re-coding a Factor – case_when

We have similar functionality with the case_when() function…

eras_data |>
  mutate(Album = case_when(Album == "Folklore" ~ "folklore",
                           Album == "Evermore" ~ "evermore",
                           Album == "Reputation" ~ "reputation",
                           .default = Album),
         Album = fct(Album)) |> 
  pull(Album)

 [1] Red        reputation Lover      Midnights  1989       Fearless  
 [7] reputation folklore   Midnights  evermore   evermore   Lover     
[13] Lover      Red        reputation reputation Speak Now  Red       
[19] Midnights  Fearless   1989       Midnights  Fearless   folklore  
[25] Lover     
9 Levels: Red reputation Lover Midnights 1989 Fearless folklore ... Speak Now

Collapsing a Factor –fct_collapse

Collapse multiple existing levels of a factor with the syntax <new level> = c(<old levels>).

eras_data |> 
  mutate(Genre = fct_collapse(.f= Album,
                       "country pop" = c("Taylor Swift", "Fearless"),
                       "pop rock" = c("Speak Now","Red"),
                       "electropop" = c("1989","reputation","Lover"),
                       "folk pop" = c("folklore","evermore"),
                       "alt-pop" = "Midnights")) |> 
  slice_sample(n = 6)

# A tibble: 6 × 3
  Song                                    Album      Genre      
  <chr>                                   <fct>      <fct>      
1 willow                                  evermore   folk pop   
2 You Belong With Me                      Fearless   country pop
3 Lavender Haze                           Midnights  alt-pop    
4 We Are Never Ever Getting Back Together Red        pop rock   
5 illicit affairs                         folklore   folk pop   
6 Look What You Made Me Do                reputation electropop 

Re-leveling a Factor –fct_relevel

Change the order of the levels of an existing factor.

eras_data |>
  pull(Album) |> 
  levels()

 [1] "Taylor Swift"                  "Fearless"                     
 [3] "Speak Now"                     "Red"                          
 [5] "1989"                          "reputation"                   
 [7] "Lover"                         "folklore"                     
 [9] "evermore"                      "Midnights"                    
[11] "The Tortured Poets Department"

eras_data |> 
  pull(Album) |>
  fct_relevel(c("Fearless","1989","Taylor Swift",
                "Speak Now","Red","Midnights","reputation",
                "folklore","Lover","evermore")) |> 
  levels()

 [1] "Fearless"                      "1989"                         
 [3] "Taylor Swift"                  "Speak Now"                    
 [5] "Red"                           "Midnights"                    
 [7] "reputation"                    "folklore"                     
 [9] "Lover"                         "evermore"                     
[11] "The Tortured Poets Department"

Re-ordering Factors in ggplot2

full_eras |> 
  mutate(Album = fct(Album)) |> 
  ggplot() +
  geom_bar(aes(y = Album,
               fill = Album)) +
  theme_minimal() +
  theme(legend.position = "none") +
  labs(x = "Number of Songs",
       y = "",
       subtitle = "Album",
       title = "Songs Played on the Eras Tour")

full_eras |> 
  mutate(Album = fct(Album,
                     levels = c("Fearless","Speak Now","Red",
                                "1989","Reputation","Lover",
                                "Folklore","Evermore",
                                "Midnights"))) |> 
  ggplot() +
  geom_bar(aes(y = Album,
               fill = Album)) +
  theme_minimal() +
  theme(legend.position = "none") +
  labs(x = "Number of Songs",
       y = "",
       subtitle = "Album",
       title = "Songs Played on the Eras Tour")

Re-ordering Factors in ggplot2

full_eras |> 
  ggplot(aes(x = Length, 
             y = Album, 
             fill = Album)) +
  geom_density_ridges() +
  theme_minimal() +
  theme(legend.position = "none")+
  labs(x = "Song Length (mins)",
       y = "",
       subtitle = "Album",
       title = "Songs Played on the Eras Tour")

full_eras |> 
  ggplot(aes(x = Length, 
             y = fct_reorder(.f = Album,
                             .x = Length,
                             .fun = mean), 
             fill = Album)) +
  geom_density_ridges() +
  theme_minimal() +
  theme(legend.position = "none")+
  labs(x = "Song Length (mins)",
       y = "",
       subtitle = "Album",
       title = "Songs Played on the Eras Tour")

Re-ordering Factors in ggplot2

full_eras |> 
  filter(!Album %in% c("1989","Fearless")) |> 
  group_by(Album, Single) |> 
  summarise(avg_len = mean(Length)) |> 
  ggplot(aes(x = Single, 
             y = avg_len, 
             color = Album)) +
  geom_point(size = 1.5) +
  geom_line() +
  theme_minimal() +
  scale_x_continuous(breaks = c(0,1),
                     labels = c("No","Yes")) +
  labs(y = "",
       subtitle = "Average Song Length",
       color = "Album")

full_eras |> 
  filter(!Album %in% c("1989","Fearless")) |> 
  group_by(Album, Single) |> 
  summarise(avg_len = mean(Length)) |> 
  ggplot(aes(x = Single, 
             y = avg_len, 
             color = fct_reorder2(.f = Album,
                                  .x = Single,
                                  .y = avg_len))) +
  geom_point(size = 1.5) +
  geom_line() +
  theme_minimal() +
  scale_x_continuous(breaks = c(0,1),
                     labels = c("No","Yes")) +
  labs(y = "",
       subtitle = "Average Song Length",
       color = "Album")

Clean Variable Names with janitor

Data from external sources likely has variable names not ideally formatted for R.

Names may…

• contain spaces.
• start with numbers.
• start with a mix of capital and lower case letters.

names(military)[1:12]

 [1] "Country"        "Notes"          "Reporting year" "1988"          
 [5] "1989"           "1990"           "1991"           "1992"          
 [9] "1993"           "1994"           "1995"           "1996"          

Clean Variable Names with janitor

The janitor package converts all variable names in a dataset to snake_case.

Names will…

• start with a lower case letter.
• have spaces filled in with _.

library(janitor)
military_clean_names <- military |> 
  clean_names()

names(military_clean_names)[1:12]

 [1] "country"        "notes"          "reporting_year" "x1988"         
 [5] "x1989"          "x1990"          "x1991"          "x1992"         
 [9] "x1993"          "x1994"          "x1995"          "x1996"         

Lifceycle Stages

As packages get updated, the functions and function arguments included in those packages will change.

• The accepted syntax for a function may change.
• A function/functionality may disappear.

Learn more about lifecycle stages of packages, functions, function arguments in R.

Lifceycle Stages

Deprecated Functions

A deprecated functionality has a better alternative available and is scheduled for removal.

• You get a warning telling you what to use instead.

military_clean |> 
  filter(across(Notes:`2019`, is.na)) |> 
  slice_head(n = 3) |> 
  select(1:8)

Warning: Using `across()` in `filter()` was deprecated in dplyr 1.0.8.
ℹ Please use `if_any()` or `if_all()` instead.

# A tibble: 3 × 8
  Country      Notes `Reporting year` `1988` `1989` `1990` `1991` `1992`
  <chr>        <chr> <chr>            <chr>  <chr>  <chr>  <chr>  <chr> 
1 Africa       <NA>  <NA>             <NA>   <NA>   <NA>   <NA>   <NA>  
2 North Africa <NA>  <NA>             <NA>   <NA>   <NA>   <NA>   <NA>  
3 Sub-Saharan  <NA>  <NA>             <NA>   <NA>   <NA>   <NA>   <NA>  

Deprecated Functions

You should not use deprecated functions!

Instead, we use…

military_clean |>
  filter(if_all(Notes:`2019`, ~ is.na(.x))) |> 
  slice_head(n = 3) |> 
  select(1:8)

# A tibble: 3 × 8
  Country      Notes `Reporting year` `1988` `1989` `1990` `1991` `1992`
  <chr>        <chr> <chr>            <chr>  <chr>  <chr>  <chr>  <chr> 
1 Africa       <NA>  <NA>             <NA>   <NA>   <NA>   <NA>   <NA>  
2 North Africa <NA>  <NA>             <NA>   <NA>   <NA>   <NA>   <NA>  
3 Sub-Saharan  <NA>  <NA>             <NA>   <NA>   <NA>   <NA>   <NA>  

Superceded Functions

A superseded functionality has a better alternative, but is not going away.

• This is a softer alternative to deprecation.
• A superseded function will not give a warning (since there’s no risk if you keep using it), but the documentation will give you a recommendation.

Lab 4: Childcare Costs in California

To do…

• Lab 4: Childcare Costs in California
  • Due Saturday (4/27) at 11:59pm
• Read Chapter 5: Strings + Dates
  • Check-in 5.1 - 5.2 due Monday (4/29) at 10:00am